﻿
namespace SharpSoft.Office
{
    using Newtonsoft.Json;
    using Newtonsoft.Json.Linq;
    using NPOI;
    using NPOI.HPSF;
    using NPOI.HSSF.UserModel;
    using NPOI.HSSF.Util;
    using NPOI.SS.Converter;
    using NPOI.SS.Formula.Eval;
    using NPOI.SS.UserModel;
    using NPOI.SS.Util;
    using NPOI.XSSF.Model;
    using NPOI.XSSF.UserModel;
    using System;
    using System.Collections.Generic;
    using System.Text;

    public class ExcelToJsonConverter
    {
        private readonly JObject excel = new JObject();
        public ExcelToJsonConverter()
        {

        }
        public static string Process(string excelFile)
        {
            IWorkbook workbook = WorkbookFactory.Create(excelFile);
            ExcelToJsonConverter excelToJsonConverter = new ExcelToJsonConverter();
            JObject obj = excelToJsonConverter.ProcessWorkbook(workbook);
            return JsonConvert.SerializeObject(obj);
        }
        protected void ProcessDocumentInformation(IWorkbook workbook)
        {
            JObject sinf = null;
            if (workbook is NPOI.HSSF.UserModel.HSSFWorkbook)
            {
                sinf = new JObject();
                SummaryInformation summaryInformation = ((HSSFWorkbook)workbook).SummaryInformation;
                if (summaryInformation != null)
                {
                    if (!string.IsNullOrEmpty(summaryInformation.Title))
                        sinf["Title"] = summaryInformation.Title;

                    if (!string.IsNullOrEmpty(summaryInformation.Author))
                        sinf["Author"] = summaryInformation.Author;

                    if (!string.IsNullOrEmpty(summaryInformation.Keywords))
                        sinf["Keywords"] = summaryInformation.Keywords;

                    if (!string.IsNullOrEmpty(summaryInformation.Comments))
                        sinf["Comments"] = summaryInformation.Comments;
                }
            }
            else if (workbook is NPOI.XSSF.UserModel.XSSFWorkbook)
            {
                sinf = new JObject();
                POIXMLProperties props = ((NPOI.XSSF.UserModel.XSSFWorkbook)workbook).GetProperties();
                if (!string.IsNullOrEmpty(props.CoreProperties.Title))
                {
                    sinf["Title"] = props.CoreProperties.Title;
                }
                if (!string.IsNullOrEmpty(props.CoreProperties.Creator))
                    sinf["Author"] = props.CoreProperties.Creator;

                if (!string.IsNullOrEmpty(props.CoreProperties.Keywords))
                    sinf["Keywords"] = props.CoreProperties.Keywords;

                if (!string.IsNullOrEmpty(props.CoreProperties.Description))
                    sinf["Comments"] = props.CoreProperties.Description;
            }
            excel["SummaryInformation"] = sinf;
        }
        public JObject ProcessWorkbook(IWorkbook workbook)
        {

            ProcessDocumentInformation(workbook);
            var sheetobjs = new JArray();
            for (int s = 0; s < workbook.NumberOfSheets; s++)
            {
                ISheet sheet = (ISheet)workbook.GetSheetAt(s);
                var sheetobj = ProcessSheet(sheet);
                if (sheetobj != null)
                {
                    sheetobjs.Add(sheetobj);
                }

            }
            excel["Sheets"] = sheetobjs;
            return excel;
        }
        public JObject ProcessSheet(ISheet sheet)
        {
            if (sheet.PhysicalNumberOfRows <= 0)
                return null;
            JObject sheetobj = new JObject();

            JArray rowobjs = new JArray();
            sheetobj["Name"] = sheet.SheetName;
            sheetobj["RowsCount"] = sheet.PhysicalNumberOfRows;


            int maxcellcount = 0;
            for (int r = 0; r < sheet.PhysicalNumberOfRows; r++)
            {
                IRow row = sheet.GetRow(r);

                if (row == null)
                    continue;
                JObject rowobj = new JObject();
                rowobj["Hidden"] = row.ZeroHeight;//隐藏行
                rowobj["Height"] = row.Height;


                CellRangeAddress[][] mergedRanges = ExcelToHtmlUtils.BuildMergedRangesMap(sheet);
                var cellcount = ProcessRow(mergedRanges, row, rowobj);
                rowobjs.Add(rowobj);
                maxcellcount = Math.Max(cellcount, maxcellcount);
            }
            JArray columnobjs = new JArray();
            for (int i = 0; i < maxcellcount; i++)
            {
                var columnobj = new JObject();
                columnobj["Width"] = sheet.GetColumnWidth(i);
                columnobj["Hidden"] = sheet.IsColumnHidden(i);
                columnobjs.Add(columnobj);
            }
            sheetobj["ColumnsCount"] = maxcellcount;
            sheetobj["Columns"] = columnobjs;
            sheetobj["Rows"] = rowobjs;
            return sheetobj;
        }
        public int ProcessRow(CellRangeAddress[][] mergedRanges, IRow row,
           JObject rowobj)
        {
            ISheet sheet = (ISheet)row.Sheet;
            int maxColIx = row.LastCellNum;
            if (maxColIx <= 0)
                return -1;

            var cellobjs = new JArray();
            rowobj["Cells"] = cellobjs;
            for (int colIx = 0; colIx < maxColIx; colIx++)
            {
                var cellobj = new JObject();

                CellRangeAddress range = ExcelToHtmlUtils.GetMergedRange(
                        mergedRanges, row.RowNum, colIx);

                if (range != null && (range.FirstColumn != colIx || range.FirstRow != row.RowNum))
                    continue;

                ICell cell = (ICell)row.GetCell(colIx);


                if (range != null)
                {
                    if (range.FirstColumn != range.LastColumn)
                        cellobj["ColumnSpan"] = (range.LastColumn - range.FirstColumn + 1);
                    if (range.FirstRow != range.LastRow)
                        cellobj["RowSpan"] = (range.LastRow - range.FirstRow + 1);
                }

                if (cell != null)
                {
                    var cellComment = sheet.GetCellComment(cell.RowIndex, cell.ColumnIndex);
                    if (cellComment != null)
                    {
                        if (cellComment.String != null)
                        {
                            cellobj["Comment"] = cellComment.String.String;
                        }

                    }
                    ProcessCell(cell, cellobj);
                }
                else
                {
                    cellobj = null;
                }
                cellobjs.Add(cellobj);

            }
            return maxColIx + 1;//返回当前行的列数

        }
        private DataFormatter _formatter = new DataFormatter();
        protected void ProcessCell(ICell cell, JObject cellobj)
        {
            ICellStyle cellStyle = cell.CellStyle as ICellStyle;
            cellobj["Style"] = BuildStyle(cell.Sheet.Workbook, cellStyle);
            string value;
            switch (cell.CellType)
            {
                case CellType.String:
                    // XXX: enrich
                    value = cell.RichStringCellValue.String;
                    break;
                case CellType.Formula:
                    switch (cell.CachedFormulaResultType)
                    {
                        case CellType.String:
                            IRichTextString str = cell.RichStringCellValue;
                            if (str != null && str.Length > 0)
                            {
                                value = (str.String);
                            }
                            else
                            {
                                value = string.Empty;
                            }
                            break;
                        case CellType.Numeric:
                            ICellStyle style = cellStyle;
                            if (style == null)
                            {
                                value = cell.NumericCellValue.ToString();
                            }
                            else
                            {
                                value = (_formatter.FormatRawCellContents(cell.NumericCellValue, style.DataFormat, style.GetDataFormatString()));
                            }
                            break;
                        case CellType.Boolean:
                            value = cell.BooleanCellValue.ToString();
                            break;
                        case CellType.Error:
                            value = ErrorEval.GetText(cell.ErrorCellValue);
                            break;
                        default:
                            value = string.Empty;
                            break;
                    }
                    break;
                case CellType.Blank:
                    value = string.Empty;
                    break;
                case CellType.Numeric:
                    value = _formatter.FormatCellValue(cell);
                    break;
                case CellType.Boolean:
                    value = cell.BooleanCellValue.ToString();
                    break;
                case CellType.Error:
                    value = ErrorEval.GetText(cell.ErrorCellValue);
                    break;
                default:
                    value = null;
                    break;
            }

            cellobj["Text"] = value;
        }
        protected String BuildStyle(IWorkbook workbook, ICellStyle cellStyle)
        {
            StringBuilder style = new StringBuilder();

            if (workbook is HSSFWorkbook)
            {
                HSSFPalette palette = ((HSSFWorkbook)workbook).GetCustomPalette();
                style.Append("white-space: pre-wrap; ");
                ExcelToHtmlUtils.AppendAlign(style, cellStyle.Alignment);

                if (cellStyle.FillPattern == FillPattern.NoFill)
                {
                    // no fill
                }
                else if (cellStyle.FillPattern == FillPattern.SolidForeground)
                {
                    //cellStyle.
                    //HSSFColor.
                    HSSFColor foregroundColor = palette.GetColor(cellStyle.FillForegroundColor);
                    if (foregroundColor != null)
                        style.AppendFormat("background-color:{0}; ", ExcelToHtmlUtils.GetColor(foregroundColor));
                }
                else
                {
                    HSSFColor backgroundColor = palette.GetColor(cellStyle.FillBackgroundColor);
                    if (backgroundColor != null)
                        style.AppendFormat("background-color:{0}; ", ExcelToHtmlUtils.GetColor(backgroundColor));
                }
            }
            else
            {
                style.Append("white-space: pre-wrap; ");
                ExcelToHtmlUtils.AppendAlign(style, cellStyle.Alignment);
                StylesTable st = ((XSSFWorkbook)workbook).GetStylesSource();
                ThemesTable tt = st.GetTheme();
                if (cellStyle.FillPattern == FillPattern.NoFill)
                {
                    // no fill
                }
                else if (cellStyle.FillPattern == FillPattern.SolidForeground)
                {
                    //cellStyle
                    IndexedColors clr = IndexedColors.ValueOf(cellStyle.FillForegroundColor);
                    string hexstring = null;
                    if (clr != null)
                    {
                        hexstring = clr.HexString;
                    }
                    else
                    {
                        XSSFColor foregroundColor = (XSSFColor)cellStyle.FillForegroundColorColor;
                        if (foregroundColor != null)
                            hexstring = ExcelToHtmlUtils.GetColor(foregroundColor);
                    }
                    if (hexstring != null)
                        style.AppendFormat("background-color:{0}; ", hexstring);
                }
                else
                {
                    IndexedColors clr = IndexedColors.ValueOf(cellStyle.FillBackgroundColor);
                    string hexstring = null;
                    if (clr != null)
                    {
                        hexstring = clr.HexString;
                    }
                    else
                    {
                        XSSFColor backgroundColor = (XSSFColor)cellStyle.FillBackgroundColorColor;
                        if (backgroundColor != null)
                            hexstring = ExcelToHtmlUtils.GetColor(backgroundColor);
                    }
                    if (hexstring != null)
                        style.AppendFormat("background-color:{0}; ", hexstring);
                }

            }

            BuildStyle_Border(workbook, style, "top", cellStyle.BorderTop, cellStyle.TopBorderColor);
            BuildStyle_Border(workbook, style, "right", cellStyle.BorderRight, cellStyle.RightBorderColor);
            BuildStyle_Border(workbook, style, "bottom", cellStyle.BorderBottom, cellStyle.BottomBorderColor);
            BuildStyle_Border(workbook, style, "left", cellStyle.BorderLeft, cellStyle.LeftBorderColor);

            IFont font = cellStyle.GetFont(workbook);
            BuildStyle_Font(workbook, style, font);

            return style.ToString();
        }

        private void BuildStyle_Border(IWorkbook workbook, StringBuilder style,
                String type, BorderStyle xlsBorder, short borderColor)
        {
            if (xlsBorder == BorderStyle.None)
                return;

            StringBuilder borderStyle = new StringBuilder();
            borderStyle.Append(ExcelToHtmlUtils.GetBorderWidth(xlsBorder));
            borderStyle.Append(' ');
            borderStyle.Append(ExcelToHtmlUtils.GetBorderStyle(xlsBorder));

            if (workbook is HSSFWorkbook)
            {
                HSSFColor color = ((HSSFWorkbook)workbook).GetCustomPalette().GetColor(borderColor);
                if (color != null)
                {
                    borderStyle.Append(' ');
                    borderStyle.Append(ExcelToHtmlUtils.GetColor(color));
                }
            }
            else
            {
                IndexedColors clr = IndexedColors.ValueOf(borderColor);
                if (clr != null)
                {
                    borderStyle.Append(' ');
                    borderStyle.Append(clr.HexString);
                }
                else
                {
                    XSSFColor color = ((XSSFWorkbook)workbook).GetStylesSource().GetTheme().GetThemeColor(borderColor);
                    if (color != null)
                    {
                        borderStyle.Append(' ');
                        borderStyle.Append(ExcelToHtmlUtils.GetColor(color));
                    }
                }
            }
            style.AppendFormat("border-{0}: {1}; ", type, borderStyle);
        }

        void BuildStyle_Font(IWorkbook workbook, StringBuilder style,
                IFont font)
        {
            switch (font.Boldweight)
            {
                case (short)FontBoldWeight.Bold:
                    style.Append("font-weight: bold; ");
                    break;
                case (short)FontBoldWeight.Normal:
                    // by default, not not increase HTML size
                    // style.Append( "font-weight: normal; " );
                    break;
            }

            if (workbook is HSSFWorkbook)
            {
                HSSFColor fontColor = ((HSSFWorkbook)workbook).GetCustomPalette().GetColor(font.Color);
                if (fontColor != null)
                    style.AppendFormat("color:{0}; ", ExcelToHtmlUtils.GetColor(fontColor));
            }
            else
            {
                IndexedColors clr = IndexedColors.ValueOf(font.Color);
                string hexstring = null;
                if (clr != null)
                {
                    hexstring = clr.HexString;
                }
                else
                {
                    StylesTable st = ((XSSFWorkbook)workbook).GetStylesSource();
                    XSSFColor fontColor = null;
                    if (st != null && st.GetTheme() != null)
                    {
                        fontColor = st.GetTheme().GetThemeColor(font.Color);
                    }
                    else
                    {
                        fontColor = ((XSSFFont)font).GetXSSFColor();
                    }
                    if (fontColor != null)
                        hexstring = ExcelToHtmlUtils.GetColor(fontColor);
                }
                if (hexstring != null)
                    style.AppendFormat("color:{0}; ", hexstring);
            }
            if (font.FontHeightInPoints != 0)
                style.Append("font-size: " + font.FontHeightInPoints + "pt; ");

            if (font.IsItalic)
            {
                style.Append("font-style: italic; ");
            }
        }
    }

}

